3 * Microsoft SQL Server-specific installer.
5 * This program is free software; you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation; either version 2 of the License, or
8 * (at your option) any later version.
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
15 * You should have received a copy of the GNU General Public License along
16 * with this program; if not, write to the Free Software Foundation, Inc.,
17 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
18 * http://www.gnu.org/copyleft/gpl.html
24 use Wikimedia\Rdbms\Database
;
25 use Wikimedia\Rdbms\DBQueryError
;
26 use Wikimedia\Rdbms\DBConnectionError
;
29 * Class for setting up the MediaWiki database using Microsoft SQL Server.
34 class MssqlInstaller
extends DatabaseInstaller
{
36 protected $globalNames = [
43 'wgDBWindowsAuthentication',
46 protected $internalDefaults = [
47 '_InstallUser' => 'sa',
48 '_InstallWindowsAuthentication' => 'sqlauth',
49 '_WebWindowsAuthentication' => 'sqlauth',
52 // SQL Server 2005 RTM
53 // @todo Are SQL Express version numbers different?)
54 public static $minimumVersion = '9.00.1399';
55 protected static $notMinimumVersionMessage = 'config-mssql-old';
57 // These are schema-level privs
58 // Note: the web user will be created will full permissions if possible, this permission
59 // list is only used if we are unable to grant full permissions.
60 public $webUserPrivs = [
71 public function getName() {
78 public function isCompiled() {
79 return self
::checkExtension( 'sqlsrv' );
85 public function getConnectForm() {
86 if ( $this->getVar( '_InstallWindowsAuthentication' ) == 'windowsauth' ) {
87 $displayStyle = 'display: none;';
89 $displayStyle = 'display: block;';
92 return $this->getTextBox(
96 $this->parent
->getHelpBox( 'config-db-host-help' )
98 Html
::openElement( 'fieldset' ) .
99 Html
::element( 'legend', [], wfMessage( 'config-db-wiki-settings' )->text() ) .
100 $this->getTextBox( 'wgDBname', 'config-db-name', [ 'dir' => 'ltr' ],
101 $this->parent
->getHelpBox( 'config-db-name-help' ) ) .
102 $this->getTextBox( 'wgDBmwschema', 'config-db-schema', [ 'dir' => 'ltr' ],
103 $this->parent
->getHelpBox( 'config-db-schema-help' ) ) .
104 $this->getTextBox( 'wgDBprefix', 'config-db-prefix', [ 'dir' => 'ltr' ],
105 $this->parent
->getHelpBox( 'config-db-prefix-help' ) ) .
106 Html
::closeElement( 'fieldset' ) .
107 Html
::openElement( 'fieldset' ) .
108 Html
::element( 'legend', [], wfMessage( 'config-db-install-account' )->text() ) .
109 $this->getRadioSet( [
110 'var' => '_InstallWindowsAuthentication',
111 'label' => 'config-mssql-auth',
112 'itemLabelPrefix' => 'config-mssql-',
113 'values' => [ 'sqlauth', 'windowsauth' ],
116 'class' => 'showHideRadio',
117 'rel' => 'dbCredentialBox',
120 'class' => 'hideShowRadio',
121 'rel' => 'dbCredentialBox',
124 'help' => $this->parent
->getHelpBox( 'config-mssql-install-auth' )
126 Html
::openElement( 'div', [ 'id' => 'dbCredentialBox', 'style' => $displayStyle ] ) .
129 'config-db-username',
131 $this->parent
->getHelpBox( 'config-db-install-username' )
133 $this->getPasswordBox(
135 'config-db-password',
137 $this->parent
->getHelpBox( 'config-db-install-password' )
139 Html
::closeElement( 'div' ) .
140 Html
::closeElement( 'fieldset' );
143 public function submitConnectForm() {
144 // Get variables from the request.
145 $newValues = $this->setVarsFromRequest( [
153 $status = Status
::newGood();
154 if ( !strlen( $newValues['wgDBserver'] ) ) {
155 $status->fatal( 'config-missing-db-host' );
157 if ( !strlen( $newValues['wgDBname'] ) ) {
158 $status->fatal( 'config-missing-db-name' );
159 } elseif ( !preg_match( '/^[a-z0-9_]+$/i', $newValues['wgDBname'] ) ) {
160 $status->fatal( 'config-invalid-db-name', $newValues['wgDBname'] );
162 if ( !preg_match( '/^[a-z0-9_]*$/i', $newValues['wgDBmwschema'] ) ) {
163 $status->fatal( 'config-invalid-schema', $newValues['wgDBmwschema'] );
165 if ( !preg_match( '/^[a-z0-9_]*$/i', $newValues['wgDBprefix'] ) ) {
166 $status->fatal( 'config-invalid-db-prefix', $newValues['wgDBprefix'] );
168 if ( !$status->isOK() ) {
172 // Check for blank schema and remap to dbo
173 if ( $newValues['wgDBmwschema'] === '' ) {
174 $this->setVar( 'wgDBmwschema', 'dbo' );
178 $this->setVarsFromRequest( [
181 '_InstallWindowsAuthentication'
185 $status = $this->getConnection();
186 if ( !$status->isOK() ) {
190 * @var Database $conn
192 $conn = $status->value
;
195 return static::meetsMinimumRequirement( $conn->getServerVersion() );
201 public function openConnection() {
202 global $wgDBWindowsAuthentication;
203 $status = Status
::newGood();
204 $user = $this->getVar( '_InstallUser' );
205 $password = $this->getVar( '_InstallPassword' );
207 if ( $this->getVar( '_InstallWindowsAuthentication' ) == 'windowsauth' ) {
208 // Use Windows authentication for this connection
209 $wgDBWindowsAuthentication = true;
211 $wgDBWindowsAuthentication = false;
215 /** @var DatabaseMssql $db */
216 $db = Database
::factory( 'mssql', [
217 'host' => $this->getVar( 'wgDBserver' ),
218 'port' => $this->getVar( 'wgDBport' ),
220 'password' => $password,
223 'schema' => $this->getVar( 'wgDBmwschema' ),
224 'tablePrefix' => $this->getVar( 'wgDBprefix' ) ] );
225 $db->prepareStatements( false );
226 $db->scrollableCursor( false );
227 $status->value
= $db;
228 } catch ( DBConnectionError
$e ) {
229 $status->fatal( 'config-connection-error', $e->getMessage() );
235 public function preUpgrade() {
236 global $wgDBuser, $wgDBpassword;
238 $status = $this->getConnection();
239 if ( !$status->isOK() ) {
240 $this->parent
->showStatusMessage( $status );
245 * @var Database $conn
247 $conn = $status->value
;
248 $conn->selectDB( $this->getVar( 'wgDBname' ) );
250 # Normal user and password are selected after this step, so for now
251 # just copy these two
252 $wgDBuser = $this->getVar( '_InstallUser' );
253 $wgDBpassword = $this->getVar( '_InstallPassword' );
257 * Return true if the install user can create accounts
261 public function canCreateAccounts() {
262 $status = $this->getConnection();
263 if ( !$status->isOK() ) {
266 /** @var Database $conn */
267 $conn = $status->value
;
269 // We need the server-level ALTER ANY LOGIN permission to create new accounts
270 $res = $conn->query( "SELECT permission_name FROM sys.fn_my_permissions( NULL, 'SERVER' )" );
272 'ALTER ANY LOGIN' => false,
273 'CONTROL SERVER' => false,
276 foreach ( $res as $row ) {
277 $serverPrivs[$row->permission_name
] = true;
280 if ( !$serverPrivs['ALTER ANY LOGIN'] ) {
284 // Check to ensure we can grant everything needed as well
285 // We can't actually tell if we have WITH GRANT OPTION for a given permission, so we assume we do
286 // and just check for the permission
287 // https://technet.microsoft.com/en-us/library/ms178569.aspx
288 // The following array sets up which permissions imply whatever permissions we specify
290 // schema database server
291 'DELETE' => [ 'DELETE', 'CONTROL SERVER' ],
292 'EXECUTE' => [ 'EXECUTE', 'CONTROL SERVER' ],
293 'INSERT' => [ 'INSERT', 'CONTROL SERVER' ],
294 'SELECT' => [ 'SELECT', 'CONTROL SERVER' ],
295 'UPDATE' => [ 'UPDATE', 'CONTROL SERVER' ],
298 $grantOptions = array_flip( $this->webUserPrivs
);
300 // Check for schema and db-level permissions, but only if the schema/db exists
301 $schemaPrivs = $dbPrivs = [
309 $dbPrivs['ALTER ANY USER'] = false;
311 if ( $this->databaseExists( $this->getVar( 'wgDBname' ) ) ) {
312 $conn->selectDB( $this->getVar( 'wgDBname' ) );
313 $res = $conn->query( "SELECT permission_name FROM sys.fn_my_permissions( NULL, 'DATABASE' )" );
315 foreach ( $res as $row ) {
316 $dbPrivs[$row->permission_name
] = true;
319 // If the db exists, we need ALTER ANY USER privs on it to make a new user
320 if ( !$dbPrivs['ALTER ANY USER'] ) {
324 if ( $this->schemaExists( $this->getVar( 'wgDBmwschema' ) ) ) {
325 // wgDBmwschema is validated to only contain alphanumeric + underscore, so this is safe
326 $res = $conn->query( "SELECT permission_name FROM sys.fn_my_permissions( "
327 . "'{$this->getVar( 'wgDBmwschema' )}', 'SCHEMA' )" );
329 foreach ( $res as $row ) {
330 $schemaPrivs[$row->permission_name
] = true;
335 // Now check all the grants we'll need to be doing to see if we can
336 foreach ( $this->webUserPrivs
as $permission ) {
337 if ( ( isset( $schemaPrivs[$permission] ) && $schemaPrivs[$permission] )
338 ||
( isset( $dbPrivs[$implied[$permission][0]] )
339 && $dbPrivs[$implied[$permission][0]] )
340 ||
( isset( $serverPrivs[$implied[$permission][1]] )
341 && $serverPrivs[$implied[$permission][1]] )
343 unset( $grantOptions[$permission] );
347 if ( count( $grantOptions ) ) {
348 // Can't grant everything
358 public function getSettingsForm() {
359 if ( $this->canCreateAccounts() ) {
360 $noCreateMsg = false;
362 $noCreateMsg = 'config-db-web-no-create-privs';
365 $wrapperStyle = $this->getVar( '_SameAccount' ) ?
'display: none' : '';
366 $displayStyle = $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth'
369 $s = Html
::openElement( 'fieldset' ) .
370 Html
::element( 'legend', [], wfMessage( 'config-db-web-account' )->text() ) .
372 '_SameAccount', 'config-db-web-account-same',
373 [ 'class' => 'hideShowRadio', 'rel' => 'dbOtherAccount' ]
375 Html
::openElement( 'div', [ 'id' => 'dbOtherAccount', 'style' => $wrapperStyle ] ) .
376 $this->getRadioSet( [
377 'var' => '_WebWindowsAuthentication',
378 'label' => 'config-mssql-auth',
379 'itemLabelPrefix' => 'config-mssql-',
380 'values' => [ 'sqlauth', 'windowsauth' ],
383 'class' => 'showHideRadio',
384 'rel' => 'dbCredentialBox',
387 'class' => 'hideShowRadio',
388 'rel' => 'dbCredentialBox',
391 'help' => $this->parent
->getHelpBox( 'config-mssql-web-auth' )
393 Html
::openElement( 'div', [ 'id' => 'dbCredentialBox', 'style' => $displayStyle ] ) .
394 $this->getTextBox( 'wgDBuser', 'config-db-username' ) .
395 $this->getPasswordBox( 'wgDBpassword', 'config-db-password' ) .
396 Html
::closeElement( 'div' );
398 if ( $noCreateMsg ) {
399 $s .= $this->parent
->getWarningBox( wfMessage( $noCreateMsg )->plain() );
401 $s .= $this->getCheckBox( '_CreateDBAccount', 'config-db-web-create' );
404 $s .= Html
::closeElement( 'div' ) . Html
::closeElement( 'fieldset' );
412 public function submitSettingsForm() {
413 $this->setVarsFromRequest( [
418 '_WebWindowsAuthentication'
421 if ( $this->getVar( '_SameAccount' ) ) {
422 $this->setVar( '_WebWindowsAuthentication', $this->getVar( '_InstallWindowsAuthentication' ) );
423 $this->setVar( 'wgDBuser', $this->getVar( '_InstallUser' ) );
424 $this->setVar( 'wgDBpassword', $this->getVar( '_InstallPassword' ) );
427 if ( $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth' ) {
428 $this->setVar( 'wgDBuser', '' );
429 $this->setVar( 'wgDBpassword', '' );
430 $this->setVar( 'wgDBWindowsAuthentication', true );
432 $this->setVar( 'wgDBWindowsAuthentication', false );
435 if ( $this->getVar( '_CreateDBAccount' )
436 && $this->getVar( '_WebWindowsAuthentication' ) == 'sqlauth'
437 && strval( $this->getVar( 'wgDBpassword' ) ) == ''
439 return Status
::newFatal( 'config-db-password-empty', $this->getVar( 'wgDBuser' ) );
442 // Validate the create checkbox
443 $canCreate = $this->canCreateAccounts();
445 $this->setVar( '_CreateDBAccount', false );
448 $create = $this->getVar( '_CreateDBAccount' );
452 // Test the web account
453 $user = $this->getVar( 'wgDBuser' );
454 $password = $this->getVar( 'wgDBpassword' );
456 if ( $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth' ) {
457 $user = 'windowsauth';
458 $password = 'windowsauth';
462 Database
::factory( 'mssql', [
463 'host' => $this->getVar( 'wgDBserver' ),
465 'password' => $password,
468 'tablePrefix' => $this->getVar( 'wgDBprefix' ),
469 'schema' => $this->getVar( 'wgDBmwschema' ),
471 } catch ( DBConnectionError
$e ) {
472 return Status
::newFatal( 'config-connection-error', $e->getMessage() );
476 return Status
::newGood();
479 public function preInstall() {
480 # Add our user callback to installSteps, right before the tables are created.
483 'callback' => [ $this, 'setupUser' ],
485 $this->parent
->addInstallStep( $callback, 'tables' );
491 public function setupDatabase() {
492 $status = $this->getConnection();
493 if ( !$status->isOK() ) {
496 /** @var Database $conn */
497 $conn = $status->value
;
498 $dbName = $this->getVar( 'wgDBname' );
499 $schemaName = $this->getVar( 'wgDBmwschema' );
500 if ( !$this->databaseExists( $dbName ) ) {
502 "CREATE DATABASE " . $conn->addIdentifierQuotes( $dbName ),
506 $conn->selectDB( $dbName );
507 if ( !$this->schemaExists( $schemaName ) ) {
509 "CREATE SCHEMA " . $conn->addIdentifierQuotes( $schemaName ),
513 if ( !$this->catalogExists( $schemaName ) ) {
515 "CREATE FULLTEXT CATALOG " . $conn->addIdentifierQuotes( $schemaName ),
519 $this->setupSchemaVars();
527 public function setupUser() {
528 $dbUser = $this->getVar( 'wgDBuser' );
529 if ( $dbUser == $this->getVar( '_InstallUser' )
530 ||
( $this->getVar( '_InstallWindowsAuthentication' ) == 'windowsauth'
531 && $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth' ) ) {
532 return Status
::newGood();
534 $status = $this->getConnection();
535 if ( !$status->isOK() ) {
539 $this->setupSchemaVars();
540 $dbName = $this->getVar( 'wgDBname' );
541 $this->db
->selectDB( $dbName );
542 $password = $this->getVar( 'wgDBpassword' );
543 $schemaName = $this->getVar( 'wgDBmwschema' );
545 if ( $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth' ) {
546 $dbUser = 'windowsauth';
547 $password = 'windowsauth';
550 if ( $this->getVar( '_CreateDBAccount' ) ) {
553 $tryToCreate = false;
556 $escUser = $this->db
->addIdentifierQuotes( $dbUser );
557 $escDb = $this->db
->addIdentifierQuotes( $dbName );
558 $escSchema = $this->db
->addIdentifierQuotes( $schemaName );
559 $grantableNames = [];
560 if ( $tryToCreate ) {
561 $escPass = $this->db
->addQuotes( $password );
563 if ( !$this->loginExists( $dbUser ) ) {
566 $this->db
->selectDB( 'master' );
567 $logintype = $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth'
569 : "WITH PASSWORD = $escPass";
570 $this->db
->query( "CREATE LOGIN $escUser $logintype" );
571 $this->db
->selectDB( $dbName );
572 $this->db
->query( "CREATE USER $escUser FOR LOGIN $escUser WITH DEFAULT_SCHEMA = $escSchema" );
574 $grantableNames[] = $dbUser;
575 } catch ( DBQueryError
$dqe ) {
576 $this->db
->rollback();
577 $status->warning( 'config-install-user-create-failed', $dbUser, $dqe->getMessage() );
579 } elseif ( !$this->userExists( $dbUser ) ) {
582 $this->db
->selectDB( $dbName );
583 $this->db
->query( "CREATE USER $escUser FOR LOGIN $escUser WITH DEFAULT_SCHEMA = $escSchema" );
585 $grantableNames[] = $dbUser;
586 } catch ( DBQueryError
$dqe ) {
587 $this->db
->rollback();
588 $status->warning( 'config-install-user-create-failed', $dbUser, $dqe->getMessage() );
591 $status->warning( 'config-install-user-alreadyexists', $dbUser );
592 $grantableNames[] = $dbUser;
596 // Try to grant to all the users we know exist or we were able to create
597 $this->db
->selectDB( $dbName );
598 if ( $grantableNames ) {
600 // First try to grant full permissions
602 'BACKUP DATABASE', 'BACKUP LOG', 'CREATE FUNCTION', 'CREATE PROCEDURE',
603 'CREATE TABLE', 'CREATE VIEW', 'CREATE FULLTEXT CATALOG', 'SHOWPLAN'
605 $fullPrivList = implode( ', ', $fullPrivArr );
607 $this->db
->query( "GRANT $fullPrivList ON DATABASE :: $escDb TO $escUser", __METHOD__
);
608 $this->db
->query( "GRANT CONTROL ON SCHEMA :: $escSchema TO $escUser", __METHOD__
);
610 } catch ( DBQueryError
$dqe ) {
611 // If that fails, try to grant the limited subset specified in $this->webUserPrivs
613 $privList = implode( ', ', $this->webUserPrivs
);
614 $this->db
->rollback();
616 $this->db
->query( "GRANT $privList ON SCHEMA :: $escSchema TO $escUser", __METHOD__
);
618 } catch ( DBQueryError
$dqe ) {
619 $this->db
->rollback();
620 $status->fatal( 'config-install-user-grant-failed', $dbUser, $dqe->getMessage() );
622 // Also try to grant SHOWPLAN on the db, but don't fail if we can't
623 // (just makes a couple things in mediawiki run slower since
624 // we have to run SELECT COUNT(*) instead of getting the query plan)
626 $this->db
->query( "GRANT SHOWPLAN ON DATABASE :: $escDb TO $escUser", __METHOD__
);
627 } catch ( DBQueryError
$dqe ) {
635 public function createTables() {
636 $status = parent
::createTables();
638 // Do last-minute stuff like fulltext indexes (since they can't be inside a transaction)
639 if ( $status->isOK() ) {
640 $searchindex = $this->db
->tableName( 'searchindex' );
641 $schema = $this->db
->addIdentifierQuotes( $this->getVar( 'wgDBmwschema' ) );
643 $this->db
->query( "CREATE FULLTEXT INDEX ON $searchindex (si_title, si_text) "
644 . "KEY INDEX si_page ON $schema" );
645 } catch ( DBQueryError
$dqe ) {
646 $status->fatal( 'config-install-tables-failed', $dqe->getMessage() );
653 public function getGlobalDefaults() {
654 // The default $wgDBmwschema is null, which breaks Postgres and other DBMSes that require
655 // the use of a schema, so we need to set it here
656 return array_merge( parent
::getGlobalDefaults(), [
657 'wgDBmwschema' => 'mediawiki',
662 * Try to see if the login exists
663 * @param string $user Username to check
666 private function loginExists( $user ) {
667 $res = $this->db
->selectField( 'sys.sql_logins', 1, [ 'name' => $user ] );
672 * Try to see if the user account exists
673 * We assume we already have the appropriate database selected
674 * @param string $user Username to check
677 private function userExists( $user ) {
678 $res = $this->db
->selectField( 'sys.sysusers', 1, [ 'name' => $user ] );
683 * Try to see if a given database exists
684 * @param string $dbName Database name to check
687 private function databaseExists( $dbName ) {
688 $res = $this->db
->selectField( 'sys.databases', 1, [ 'name' => $dbName ] );
693 * Try to see if a given schema exists
694 * We assume we already have the appropriate database selected
695 * @param string $schemaName Schema name to check
698 private function schemaExists( $schemaName ) {
699 $res = $this->db
->selectField( 'sys.schemas', 1, [ 'name' => $schemaName ] );
704 * Try to see if a given fulltext catalog exists
705 * We assume we already have the appropriate database selected
706 * @param string $catalogName Catalog name to check
709 private function catalogExists( $catalogName ) {
710 $res = $this->db
->selectField( 'sys.fulltext_catalogs', 1, [ 'name' => $catalogName ] );
715 * Get variables to substitute into tables.sql and the SQL patch files.
719 public function getSchemaVars() {
721 'wgDBname' => $this->getVar( 'wgDBname' ),
722 'wgDBmwschema' => $this->getVar( 'wgDBmwschema' ),
723 'wgDBuser' => $this->getVar( 'wgDBuser' ),
724 'wgDBpassword' => $this->getVar( 'wgDBpassword' ),
728 public function getLocalSettings() {
729 $schema = LocalSettingsGenerator
::escapePhpString( $this->getVar( 'wgDBmwschema' ) );
730 $prefix = LocalSettingsGenerator
::escapePhpString( $this->getVar( 'wgDBprefix' ) );
731 $windowsauth = $this->getVar( 'wgDBWindowsAuthentication' ) ?
'true' : 'false';
733 return "# MSSQL specific settings
734 \$wgDBWindowsAuthentication = {$windowsauth};
735 \$wgDBmwschema = \"{$schema}\";
736 \$wgDBprefix = \"{$prefix}\";";